package com.example.back.mapper.otd;

import com.example.back.entity.otd.Posts;
import com.example.back.mapper.PostsSqlProvider;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.SelectProvider;

import java.util.ArrayList;

@Mapper
public interface PostsMapper {
    @Select("select p.id,p.title,p.time,p.text,p.url,p.browse,p.userId ,u.yhm ,u.nc,u.tx  from post p,user u where p.userId = u.id")
    ArrayList<Posts>getPosts();
    @Select("SELECT p.id, p.title, p.time, p.text, p.url, p.browse, p.userId, u.yhm, u.nc, u.tx FROM post p JOIN user u ON p.userId = u.id JOIN relations re ON re.userId = p.userId WHERE re.fanId = #{param1}" )
    ArrayList<Posts> getPostsByfanId(Integer fanId);
    @Select("select p.id,p.title,p.time,p.text,p.url,p.browse,p.userId ,u.yhm ,u.nc,u.tx  from post p,user u where p.userId = u.id ORDER BY p.time DESC")
    ArrayList<Posts> getNewPosts();

    @Select("select " +
            "p.id, p.title, p.time, p.text, p.url, p.browse, p.userId, " +
            "u.yhm, u.nc, u.tx, " +
            // 统计当前帖子的点赞数
            "(SELECT COUNT(*) FROM `like` WHERE postId = p.id) AS likeCount, " +
            // 统计当前帖子的收藏数
            "(SELECT COUNT(*) FROM collection WHERE postId = p.id) AS collectCount " +
            "from post p, user u " +
            "where p.userId = u.id and p.id = #{param1}")
    Posts queryPostById(Integer id);
    //=======================
    @Select("SELECT " +
            "p.id, p.title, p.time, p.text, p.url, p.browse, p.userId, " +
            "u.yhm, u.nc, u.tx, " +
            "COUNT(DISTINCT c.id) AS commentCount,  " +
            "(SELECT COUNT(*) FROM `like` WHERE postId = p.id) AS likeCount, " +
            "(SELECT COUNT(*) FROM collection WHERE postId = p.id) AS collectCount " +
            "FROM post p " +
            "LEFT JOIN user u ON p.userId = u.id " +  // 关联用户信息
            "LEFT JOIN comment c ON p.id = c.postId " +  // 关联评论表（统计评论数）
            "GROUP BY p.id " +  // 按帖子 ID 分组（确保评论数统计正确）
            "ORDER BY likeCount DESC")  // 按点赞数
    ArrayList<Posts> getPostsWithAllCounts();

    @Select("SELECT " +
            "p.id, p.title, p.time, p.text, p.url, p.browse, p.userId, " +
            "u.yhm, u.nc, u.tx, " +
            "COUNT(DISTINCT c.id) AS commentCount,  " +
            "(SELECT COUNT(*) FROM `like` WHERE postId = p.id) AS likeCount, " +
            "(SELECT COUNT(*) FROM collection WHERE postId = p.id) AS collectCount " +
            "FROM post p " +
            "LEFT JOIN user u ON p.userId = u.id " +  // 关联用户信息
            "LEFT JOIN comment c ON p.id = c.postId " +  // 关联评论表（统计评论数）
            "GROUP BY p.id " +  // 按帖子 ID 分组（确保评论数统计正确）
            "ORDER BY p.time DESC")  // 按时间
    ArrayList<Posts> getPostsWithAllCountsOrderedByTime();

    @SelectProvider(type = PostsSqlProvider.class, method = "getPostsAndUserSql")
    ArrayList<Posts> getPostsAndUser(@Param("text") String text, @Param("time") String time);
}
